package 事务专题;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.concurrent.CountDownLatch;

/**
 * @author idea
 * @date 2019/7/2
 * @Version V1.0
 */
public class FantasyReadDemo {

    public static final String READ_SQL = "SELECT * FROM money";
    public static final String UPDATE_SQL = "UPDATE `money` SET `money` = ? WHERE `id` = 3;\n";


    public CountDownLatch countDownLatch=new CountDownLatch(2);

    public void readAndUpdate1() {
        try (Connection conn = JdbcUtil.getConnection();) {
            conn.setAutoCommit(false);
            PreparedStatement ps = conn.prepareStatement(READ_SQL);
            conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
            ResultSet rs = ps.executeQuery();
            rs.next();
            int currentMoney = (int) rs.getObject(2);
            System.out.println("执行写取数据操作----" + currentMoney);
            //堵塞等待唤醒
            countDownLatch.countDown();
            PreparedStatement writePs = conn.prepareStatement(UPDATE_SQL);
            writePs.setInt(1, currentMoney - 1);
            writePs.execute();
            conn.commit();
            writePs.close();
            ps.close();
            System.out.println("执行写操作结束---1");
        } catch (Exception e) {
            e.printStackTrace();
            readAndUpdate1();
        }
    }

    public void readAndUpdate2() {
        try (Connection conn = JdbcUtil.getConnection();) {
            conn.setAutoCommit(false);
            PreparedStatement ps = conn.prepareStatement(READ_SQL);
            conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
            ResultSet rs = ps.executeQuery();
            rs.next();
            int currentMoney = (int) rs.getObject(2);
            System.out.println("执行写取数据操作----" + currentMoney);
            //堵塞唤醒
            countDownLatch.countDown();
            PreparedStatement writePs = conn.prepareStatement(UPDATE_SQL);
            writePs.setInt(1, currentMoney - 1);
            writePs.execute();
            conn.commit();
            writePs.close();
            ps.close();
            System.out.println("执行写操作结束---2");
        } catch (Exception e) {
            //使用串行化事务级别能够较好的保证数据的一致性，可串行化事务 serializable 是事务的最高级别，在每个读数据上加上锁
            //innodb里面是加入了行锁，因此出现了异常的时候，只需要重新执行一遍事务即可。
            e.printStackTrace();
            readAndUpdate2();
        }
    }

    public void fantasyRead() {
        Thread thread1 = new Thread(new Runnable() {
            @Override
            public void run() {
                readAndUpdate1();
            }
        });

        Thread thread2 = new Thread(new Runnable() {
            @Override
            public void run() {
                readAndUpdate2();
            }
        });
        try {
            thread1.start();
//            Thread.sleep(500);
            thread2.start();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    public static void main(String[] args) {
        FantasyReadDemo fantasyReadDemo = new FantasyReadDemo();
        fantasyReadDemo.fantasyRead();
    }

}
